To calculate an amount within a time interval you use the Interval Aggregate functions. These functions aggregate the values of a time-varying attribute within a time interval, into a single value. You can also specify that the value of the attribute is only to be included in the aggregation if a given boolean attribute is true at that time.
In general the result of these functions will not vary over time, however, if time-varying start or end dates are passed in as parameters, the result will vary too.
The functions are: Interval Count Distinct, Interval Count Distinct If, Interval Daily Sum, Interval Daily Sum If, Interval Weighted Average, and Interval Weighted Average If.
Calculate the number of distinct values for a variable in a time period
Calculate the sum of a variable in a time period
Calculate the sum of a variable in a time period only when a condition is true
Calculate the average value of a variable in a time period
Calculate the average value of a variable in a time period when a condition is true
The Interval Count Distinct function counts the number of known distinct values for a variable, in the interval from the specified start date (inclusive) to the end date (exclusive). The syntax for this function is:
For example, the Interval Count Distinct function could be used to determine the number of distinct addresses the client had between 1 July 2005 and 30 June 2006 (inclusive). In Word you would write this rule as:
the client's distinct address count = IntervalCountDistinct(2005-07-01,2006-07-01,the client's address)
This function returns a value of 2 for 'the client's address count' for the following data where p1 is 'the client's address':
The Interval Count Distinct If function counts the number of known distinct values for an attribute, in the interval from the specified start date (inclusive) to the end date (exclusive), only including times when a boolean filter is true. The syntax for this function is:
For example, the Interval Count Distinct If function could be used to determine the number of distinct addresses the client had between 1 January 2000 and 31 December 2006 (inclusive) where the client was aged over 18. In Word you would write this rule as:
the client's distinct address count = IntervalCountDistinctIf(2000-01-01,2007-01-01,the client's address,the client is aged over 18)
This function returns a value of 3 for 'the client's distinct address count' for the following data where b5 is 'the client is aged over 18' and p12 is 'the client's address':
The Interval Daily Sum function calculates the sum of a currency or number variable, in the interval from the specified start date (inclusive) to the end date (exclusive). The attribute is assumed to be a daily quantity. The syntax for this function is:
For example, the Interval Daily Sum function could be used to sum the daily rate of benefit into the amount of benefit payable for the assessment period between 5 July 2006 and 31 July 2006 (inclusive). In Word you would write this rule as:
the amount of benefit payable for the assessment period = IntervalDailySum(2006-07-05,2006-08-01,the daily rate of benefit)
This function returns a value of $575 for 'the amount of benefit payable for the assessment period' for the following data where p2 is 'the daily rate of benefit':
That is, $15 * days from 5 July 2006 to 9 July 2006 = $15 * 5 = $75
+ $20 * days from 10 July 2006 to 19 July 2006 = $20 * 10 = $200
+ $25 * days from 20 July 2006 to 31 July 2006 = $25 * 12 = $300
Total = $575
The Interval Daily Sum If function calculates the sum of all the daily values for a currency or number variable, in the interval from the specified start date (inclusive) to the end date (exclusive), only including times when a boolean filter is true. The syntax for this function is:
For example, the Interval Daily Sum If function could be used to determine the total amount spent on weekends in December 2006. In Word you would write this rule as:
the total amount spent on weekends in December = IntervalDailySumIf(2006-12-01,2007-01-01,the daily amount spent,the day is a weekend)
This function returns a value of $530 for 'the total amount spent on weekends in December' for the following data:
That is, $30 + $50 + $45 + $15 + $65 + $40 + $75 + $100 + $70 + $40 = $530
The Weighted Average function calculates the average value of a currency or number variable in the interval from the specified start date (inclusive) to the end date (exclusive) weighted by the time span to which each value applies. The syntax for this function is:
For example, the Interval Weighted Average function could be used to determine the average number of children in care in a particular week. In Word you would write this rule as:
the average number of children in care = IntervalWeightedAverage(2007-01-22,2007-01-29,the number of children in care)
This function returns a value of 8.28571 for 'the average number of children in care' for the following data where p5 is 'the number of children in care':
The Weighted Average If function calculates the average value of a currency or number variable in the interval from the specified start date (inclusive) to the end date (exclusive), only including times when a boolean filter is true (weighted by the time span to which each value applies and where the filter is true). The syntax for this function is:
For example, the Interval Weighted Average If function could be used to determine the average number of children in care for the weekdays in a specified period. In Word you would write this rule as:
the average number of children in care for the weekdays in the assessment period = IntervalWeightedAverageIf(2007-01-22,2007-01-29,the number of children in care,the day is a weekday)
This function returns a value of 9.2 for 'the average number of children in care for the weekdays in the assessment period' for the following data where b4 is 'the day is a weekday' and p10 is 'the number of children in care':
